"""
@author: 林俊杰
@version: v1.7.23
@date: 2021/07/23
"""


# useful for handling different item types with a single interface
import pymongo
import pymysql
from itemadapter import ItemAdapter
from scrapy.utils.project import get_project_settings


class ScrapyuniversalPipeline:
    # def process_item(self, item, spider):
    #     with open('news_info.txt', 'a') as f:
    #         f.write(item['title'] + ','
    #                 + item['author'] + ','
    #                 + item['datetime'] + ','
    #                 + item['source'] + ','
    #                 + item['url'] + '\n')

    # def __init__(self):
    #     settings = get_project_settings()
    #     host = settings.get("MONGODB_HOST")
    #     port = settings.get("MONGODB_PORT")
    #     dbname = settings.get("MONGODB_DBNAME")
    #     sheetname = settings.get("MONGODB_SHEETNAME")
    #     # 创建MONGODB数据库链接
    #     client = pymongo.MongoClient(host=host, port=port)
    #     mydb = client[dbname]
    #     self.post = mydb[sheetname]
    #
    # def process_item(self, item, spider):
    #     data = dict(item)
    #     self.post.insert(data)

    def process_item(self, item, spider):
        # print("['mon_ticket'] 的类型为" + type(item['mon_ticket']))
        DB_MYSQL = spider.settings.get('DB_MYSQL')
        con = pymysql.connect(**DB_MYSQL)
        cur = con.cursor()
        # author_sql = ('insert into author(author_id, name, introduction, literature_nums, char_nums, write_nums,author_url) values (%s,%s,%s,%s,%s,%s,%s)')
        # author_lis = (item['author_id'], item['name'], item['introduction'], item['literature_nums'], item['char_nums'], item['write_nums'], "https://my.qidian.com/author/"+str(item['author_id']))
        # book_sql = ('insert into book(book_id, title, imgSrc, intro, intro_detail, char_nums, total_recomm, week_recomm, latest_chapter, latest_update_time,honor,author_id,mon_ticket,week_reward,today_reward,book_url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)')
        # book_lis = (item['book_id'], item['title'], item['imgSrc'], item['intro'], item['intro_detail'], item['char_nums'], item['total_recomm'], item['week_recomm'], item['latest_chapter'], item['latest_update_time'], item['honor'], item['author_id'], item['mon_ticket'], item['week_reward'], item['today_reward'], item['url'])

        author_sql = ('insert into author(author_id, name, introduction, literature_nums, char_total, write_nums,author_url) values (%s,%s,%s,%s,%s,%s,%s)')
        author_lis = (item['author_id'], item['name'], item['introduction'], item['literature_nums'], item['char_total'], item['write_nums'], "https://my.qidian.com/author/" + str(item['author_id']))
        book_sql = ('insert into book(book_id, title, imgSrc, intro, intro_detail,  latest_chapter, latest_update_time,honor,author_id,mon_ticket,week_reward,today_reward,book_url) values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)')
        book_lis = (item['book_id'], item['title'], item['imgSrc'], item['intro'], item['intro_detail'], item['latest_chapter'], item['latest_update_time'], item['honor'],item['author_id'], item['mon_ticket'], item['week_reward'], item['today_reward'], item['url'])

        book_id = item['book_id']
        tag_sql = ('insert into tag(content,tag_type) values (%s,%s)')
        tagrs_sql = ('insert into tag_rs(tag_id,book_id) values (%s,%s)')
        query_sql = ('select tag_id from tag where content = %s')

        try:
            for item in item['tag']:
                query_lis = (item)
                cur.execute(query_sql, query_lis)
                results = cur.fetchall()
                if len(results) == 0:
                    tag_lis = (item, 1)
                    cur.execute(tag_sql, tag_lis)
                    # con.commit()
                    cur.execute(query_sql, query_lis)
                    results = cur.fetchall()
                tag_id = results[0][0]
                tagrs_lis = (tag_id, book_id)
                cur.execute(tagrs_sql, tagrs_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        try:
            cur.execute(book_sql, book_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        try:
            cur.execute(author_sql, author_lis)
        except Exception as e:
            print("insert err:", e)
            con.rollback()
        else:
            con.commit()

        cur.close()
        con.close()

